﻿Imports System
Imports System.Xml
Imports System.Data
Imports MySql.Data.MySqlClient
Imports System.Security.Cryptography
Imports System.Text

Module Module1

    'Public AppName As String = "My Application CRUD"
    Public path = My.Application.Info.DirectoryPath & "\"
    Public pathConfig As String = path & "config.xml"

    Public Hostname As String = "localhost"
    Public Username As String = "root"
    Public Password As String = "Admin@1"
    Public DBName As String = "infotech_db"

    Public Sub appExit()
        Application.Exit()
        Application.ExitThread()
    End Sub

    Public Sub checkMultipleInstance()
        'If we find two or more running processes by the same name
        If Process.GetProcessesByName(Process.GetCurrentProcess.ProcessName).Length > 1 Then
            ' Tell the user he can only have on instance running
            MessageBox.Show("Only one instance of this program can be open at a given time", "Duplicate instance detected", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            ' Close the application
            appExit()
        End If
    End Sub

    Public Sub createXML()
        Dim xwriter As New XmlTextWriter(pathConfig, System.Text.Encoding.UTF8)

        xwriter.WriteStartDocument(True)

        xwriter.WriteStartElement("config", "DatabaseConfig")
        xwriter.WriteStartElement("Database", "DatabaseConfig")
        xwriter.WriteElementString("hostname", "DatabaseConfig", "locahost")
        xwriter.WriteElementString("username", "DatabaseConfig", "root")
        xwriter.WriteElementString("password", "DatabaseConfig", "Admin@1")
        xwriter.WriteElementString("dbname", "DatabaseConfig", "infotech_db")
        xwriter.WriteEndElement()

        xwriter.WriteStartElement("appConfig", "ApplicationConfig")
        xwriter.WriteElementString("appname", "ApplicationConfig", "My Application Basic CRUD")
        xwriter.WriteEndElement()

        xwriter.WriteEndDocument()

        xwriter.Close()

    End Sub

    Public Sub connectMySQL(ByVal Hostname As String, ByVal Username As String, ByVal Password As String, ByVal DBName As String)
        'connect to mysql dtabase server
        Dim myConnectionString As String
        myConnectionString = "server=" & Hostname & ";" & "uid=" & Username & ";" & "pwd=" & Password & ";" & "database=" & DBName & "; Charset = latin1;"
        Try
            Dim conn As New System.Data.SqlClient.SqlConnection(myConnectionString)
            conn.Open()
        Catch ex As System.Data.SqlClient.SqlException
            Select Case ex.Number
                Case 0
                    MessageBox.Show("Cannot connect to server. Contact administrator")
                    appExit()
                Case 1045
                    MessageBox.Show("Invalid username/password, please try again")
                    appExit()
            End Select
        End Try
        'complete tutorials here: http://zetcode.com/db/mysqlvb/
    End Sub

    Public Sub selectAllfromTable(ByVal table As String, ByVal argument As String)
        Dim cs As String = "server=" & Hostname & ";" & "uid=" & Username & ";" & "pwd=" & Password & ";" & "database=" & DBName & "; Charset = latin1;"

        Dim conn As New BaseCollection(cs)

        Try
            conn.Open()
            Dim stm As String = "SELECT * FROM " & table & " " & argument & ""
            Dim cmd As MySqlCommand = New MySqlCommand(stm, conn)
            Dim reader As IDataReader = cmd.ExecuteReader()
            Dim temp_id As String, temp_username As String, temp_password As String

            While reader.Read()

                temp_id = reader.GetInt32("id")
                temp_username = reader.GetString("username")
                temp_password = reader.GetString("password")
                MsgBox(temp_id & " : " & temp_username & " : " & temp_password & vbCr)
            End While


            reader.Close()

        Catch ex As SystemException
            MsgBox("Error: " & ex.ToString())
        Finally
            conn.Close()
        End Try
    End Sub

    Public Sub insertIntoTable(ByVal table As String)
        Dim cs As String = "server=" & Hostname & ";" & "uid=" & Username & ";" & "pwd=" & Password & ";" & "database=" & DBName & "; Charset = latin1;"
        Dim conn As New BaseCollection(cs)

        Dim cmd As New MySqlCommand()

        Try
            conn.Open()
            cmd.Connection = conn

            cmd.CommandText = "INSERT INTO " & table & "(username,password) VALUES(@username,@password)"
            cmd.Prepare()

            cmd.Parameters.AddWithValue("@username", "usernamenimarlon")
            cmd.Parameters.AddWithValue("@password", "passwordnimarlon")
            cmd.ExecuteNonQuery()

            conn.Close()

        Catch ex As SystemException
            MsgBox("Error: " & ex.ToString())
        End Try
    End Sub

    Public Sub deleteIntoTableWhere(ByVal table As String, ByVal uid As Integer)
        Dim cs As String = "server=" & Hostname & ";" & "uid=" & Username & ";" & "pwd=" & Password & ";" & "database=" & DBName & "; Charset = latin1;"
        Dim conn As New BaseCollection(cs)

        Dim cmd As New MySqlCommand()
        Try
            conn.Open()

            cmd.Connection = conn

            cmd.CommandText = "DELETE FROM " & table & " WHERE id = " & uid & ""
            cmd.ExecuteNonQuery()

            conn.Close()

        Catch ex As SystemException
            MsgBox("Error: " & ex.ToString())
        End Try
    End Sub

    Public Sub udpdateIntoTableWhere(ByVal table As String, ByVal fieldname As String, ByVal value As String, ByVal uid As Integer)
        Dim cs As String = "server=" & Hostname & ";" & "uid=" & Username & ";" & "pwd=" & Password & ";" & "database=" & DBName & "; Charset = latin1;"
        Dim conn As New BaseCollection(cs)

        Dim cmd As New MySqlCommand()
        Try
            conn.Open()

            cmd.Connection = conn

            cmd.CommandText = "UPDATE " & table & " SET " & fieldname & " = '" & value & "' WHERE id = " & uid & ""
            cmd.ExecuteNonQuery()
            'cmd.CommandText = "UPDATE Books SET Title = 'War and Peace' WHERE Id = 1"
            'cmd.ExecuteNonQuery()
            'cmd.CommandText = "UPDATE Books SET Titl = 'Anna Karenina' WHERE Id = 2"
            'cmd.ExecuteNonQuery()

            conn.Close()

        Catch ex As SystemException
            MsgBox("Error: " & ex.ToString())
        End Try

    End Sub

    Public Sub XMLtoMySQL()
        'check if config.xml not exists it will create one with default values
        If My.Computer.FileSystem.FileExists(pathConfig) Then
            Try
                Dim strHostname, strUsername, strPassword, strDBName, strAppName As String

                Dim xreader As XmlTextReader = Nothing
                xreader = New XmlTextReader(pathConfig)

                While (xreader.Read())

                    Dim type = xreader.NodeType
                    If (type = XmlNodeType.Element) Then    'if node type was element
                        If (xreader.Name = "hostname") Then 'if the loop found a <hostname> tag
                            strHostname = xreader.ReadInnerXml.ToString()
                        ElseIf (xreader.Name = "username") Then 'if the loop found a <username> tag
                            strUsername = xreader.ReadInnerXml.ToString()
                        ElseIf (xreader.Name = "password") Then 'if the loop found a <password> tag
                            strPassword = xreader.ReadInnerXml.ToString()
                        ElseIf (xreader.Name = "dbname") Then 'if the loop found a <dbname> tag
                            strDBName = xreader.ReadInnerXml.ToString()
                        ElseIf (xreader.Name = "appname") Then 'if the loop found a <appname> tag
                            strAppName = xreader.ReadInnerXml.ToString()
                        End If
                    End If
                End While
                connectMySQL("localhost", "root", "Admin@1", "infotech_db")

            Catch ex As Exception
                MsgBox(Err.Description)
            End Try

        Else
            'if config.xml not exists it will create one with default values
            createXML()
        End If
    End Sub

    Public Sub runMySQL()
        'check if mysql server is running
        'If no running it will sggest to turn on mysql server
        Dim strMySQL = "mysqld"
        Dim p = Process.GetProcessesByName(strMySQL)

        If p.Length > 0 Then
            'Process.Start(path & "mysql\bin\mysqld.exe")
            MsgBox("MYSQL IS RUNNING!")
        Else
            MessageBox.Show(strMySQL & " is not running!" & vbCr & "Application will exit and please try again.", "Database Server Status", MessageBoxButtons.OK, MessageBoxIcon.Error)

            Try
                Dim procStartInfo As New ProcessStartInfo
                Dim procExecuting As New Process

                With procStartInfo
                    .UseShellExecute = True
                    .FileName = path & "mysql\bin\" & strMySQL
                    .WindowStyle = ProcessWindowStyle.Hidden
                    .Verb = "runas" 'add this to prompt for elevation
                    .Arguments = " --defaults-file=mysql\bin\my.ini --standalone --console"
                End With

                procExecuting = Process.Start(procStartInfo)
                appExit()
            Catch ex As Exception
                MessageBox.Show(Err.Description)
            End Try

        End If
    End Sub

    Public Sub endMySQL()
        Try
            Dim procStartInfo As New ProcessStartInfo
            Dim procExecuting As New Process

            With procStartInfo
                .UseShellExecute = True
                .FileName = path & "mysql\bin\pv.exe"
                .WindowStyle = ProcessWindowStyle.Hidden
                .Verb = "runas" 'add this to prompt for elevation
                .Arguments = " -f -k mysqld.exe -q"
            End With

            procExecuting = Process.Start(procStartInfo)
            appExit()
        Catch ex As Exception
            MessageBox.Show(Err.Description)
        End Try
    End Sub

    Public Function GenerateHash(ByVal SourceText As String) As String
        'Create an encoding object to ensure the encoding standard for the source text
        Dim Ue As New UnicodeEncoding()
        'Retrieve a byte array based on the source text
        Dim ByteSourceText() As Byte = Ue.GetBytes(SourceText)
        'Instantiate an MD5 Provider object
        Dim Md5 As New MD5CryptoServiceProvider()
        'Compute the hash value from the source
        Dim ByteHash() As Byte = Md5.ComputeHash(ByteSourceText)
        'And convert it to String format for return
        Return Convert.ToBase64String(ByteHash)
    End Function

    Public Function getMD5Hash(ByVal strToHash As String) As String

        Dim md5Obj As New MD5CryptoServiceProvider

        Dim bytesToHash() As Byte = System.Text.Encoding.ASCII.GetBytes(strToHash)
        bytesToHash = md5Obj.ComputeHash(bytesToHash)
        Dim strResult As String = ""
        For Each b As Byte In bytesToHash
            strResult += b.ToString("x2")
        Next
        Return strResult

    End Function

    Public Function getSHA1Hash(ByVal strToHash As String) As String

        Dim md5Obj As New SHA1CryptoServiceProvider

        Dim bytesToHash() As Byte = System.Text.Encoding.ASCII.GetBytes(strToHash)
        bytesToHash = md5Obj.ComputeHash(bytesToHash)
        Dim strResult As String = ""
        For Each b As Byte In bytesToHash
            strResult += b.ToString("x2")
        Next
        Return strResult

    End Function

    Public Function getRandom(ByVal Max As Integer) As Integer
        ' Create a random number generator
        Dim Generator As System.Random = New System.Random()
        Dim MyRandom As String = ""
        MyRandom = Generator.Next(1, Max + 1) ' Note: Next function returns numbers _less than_ max, so pass in max + 1 to include max as a possible value
        Return MyRandom
    End Function

    Public Function GetUnixMicroTime(ByVal dates As DateTime) As String
        If dates.IsDaylightSavingTime = True Then
            dates = DateAdd(DateInterval.Hour, -1, dates)
        End If
        Dim origin As DateTime = New DateTime(1970, 1, 1, 0, 0, 0, 0)
        Dim diff As TimeSpan = dates - origin
        Return CStr(diff.TotalSeconds)
    End Function

    Public Function uniqID(ByVal schoolID As Integer, ByVal schoolBranchID As Integer, ByVal courseID As Integer) As String

        Dim timeStamp As String = DateTime.Now.ToString("yyyyMMddhhmmss")

        Dim i As Integer = GetUnixMicroTime(DateTime.Now)

        Dim sGUID As String
        sGUID = getMD5Hash(System.Guid.NewGuid.ToString())

        Dim uID = "0" & schoolID & "-0" & schoolBranchID & "-0" & courseID & "-" & Val(i) & "-" & sGUID.ToUpper & "-" & timeStamp

        Return uID

    End Function

    Public Class SimpleHash

        ' <summary>
        ' Generates a hash for the given plain text value and returns a
        ' base64-encoded result. Before the hash is computed, a random salt
        ' is generated and appended to the plain text. This salt is stored at
        ' the end of the hash value, so it can be used later for hash
        ' verification.
        ' </summary>
        ' <param name="plainText">
        ' Plaintext value to be hashed. The function does not check whether
        ' this parameter is null.
        ' </param>
        ' < name="hashAlgorithm">
        ' Name of the hash algorithm. Allowed values are: "MD5", "SHA1",
        ' "SHA256", "SHA384", and "SHA512" (if any other value is specified
        ' MD5 hashing algorithm will be used). This value is case-insensitive.
        ' </param>
        ' < name="saltBytes">
        ' Salt bytes. This parameter can be null, in which case a random salt
        ' value will be generated.
        ' </param>
        ' <returns>
        ' Hash value formatted as a base64-encoded string.
        ' </returns>

        Public Shared Function ComputeHash(ByVal plainText As String, _
                                           ByVal hashAlgorithm As String, _
                                           ByVal saltBytes() As Byte) _
                               As String

            ' If salt is not specified, generate it on the fly.
            If (saltBytes Is Nothing) Then

                ' Define min and max salt sizes.
                Dim minSaltSize As Integer
                Dim maxSaltSize As Integer

                minSaltSize = 4
                maxSaltSize = 8

                ' Generate a random number for the size of the salt.
                Dim random As Random
                random = New Random()

                Dim saltSize As Integer
                saltSize = random.Next(minSaltSize, maxSaltSize)

                ' Allocate a byte array, which will hold the salt.
                saltBytes = New Byte(saltSize - 1) {}

                ' Initialize a random number generator.
                Dim rng As RNGCryptoServiceProvider
                rng = New RNGCryptoServiceProvider()

                ' Fill the salt with cryptographically strong byte values.
                rng.GetNonZeroBytes(saltBytes)
            End If

            ' Convert plain text into a byte array.
            Dim plainTextBytes As Byte()
            plainTextBytes = Encoding.UTF8.GetBytes(plainText)

            ' Allocate array, which will hold plain text and salt.
            Dim plainTextWithSaltBytes() As Byte = _
                New Byte(plainTextBytes.Length + saltBytes.Length - 1) {}

            ' Copy plain text bytes into resulting array.
            Dim I As Integer
            For I = 0 To plainTextBytes.Length - 1
                plainTextWithSaltBytes(I) = plainTextBytes(I)
            Next I

            ' Append salt bytes to the resulting array.
            For I = 0 To saltBytes.Length - 1
                plainTextWithSaltBytes(plainTextBytes.Length + I) = saltBytes(I)
            Next I

            ' Because we support multiple hashing algorithms, we must define
            ' hash object as a common (abstract) base class. We will specify the
            ' actual hashing algorithm class later during object creation.
            Dim hash As HashAlgorithm

            ' Make sure hashing algorithm name is specified.
            If (hashAlgorithm Is Nothing) Then
                hashAlgorithm = ""
            End If

            ' Initialize appropriate hashing algorithm class.
            Select Case hashAlgorithm.ToUpper()

                Case "SHA1"
                    hash = New SHA1Managed()

                Case "SHA256"
                    hash = New SHA256Managed()

                Case "SHA384"
                    hash = New SHA384Managed()

                Case "SHA512"
                    hash = New SHA512Managed()

                Case Else
                    hash = New MD5CryptoServiceProvider()

            End Select

            ' Compute hash value of our plain text with appended salt.
            Dim hashBytes As Byte()
            hashBytes = hash.ComputeHash(plainTextWithSaltBytes)

            ' Create array which will hold hash and original salt bytes.
            Dim hashWithSaltBytes() As Byte = _
                                       New Byte(hashBytes.Length + _
                                                saltBytes.Length - 1) {}

            ' Copy hash bytes into resulting array.
            For I = 0 To hashBytes.Length - 1
                hashWithSaltBytes(I) = hashBytes(I)
            Next I

            ' Append salt bytes to the result.
            For I = 0 To saltBytes.Length - 1
                hashWithSaltBytes(hashBytes.Length + I) = saltBytes(I)
            Next I

            ' Convert result into a base64-encoded string.
            Dim hashValue As String
            hashValue = Convert.ToBase64String(hashWithSaltBytes)

            ' Return the result.
            ComputeHash = hashValue
        End Function

        ' <summary>
        ' Compares a hash of the specified plain text value to a given hash
        ' value. Plain text is hashed with the same salt value as the original
        ' hash.
        ' </summary>
        ' <param name="plainText">
        ' Plain text to be verified against the specified hash. The function
        ' does not check whether this parameter is null.
        ' </param>
        ' < name="hashAlgorithm">
        ' Name of the hash algorithm. Allowed values are: "MD5", "SHA1",
        ' "SHA256", "SHA384", and "SHA512" (if any other value is specified
        ' MD5 hashing algorithm will be used). This value is case-insensitive.
        ' </param>
        ' < name="hashValue">
        ' Base64-encoded hash value produced by ComputeHash function. This value
        ' includes the original salt appended to it.
        ' </param>
        ' <returns>
        ' If computed hash mathes the specified hash the function the return
        ' value is true; otherwise, the function returns false.
        ' </returns>

        Public Shared Function VerifyHash(ByVal plainText As String, _
                                          ByVal hashAlgorithm As String, _
                                          ByVal hashValue As String) _
                               As Boolean

            ' Convert base64-encoded hash value into a byte array.
            Dim hashWithSaltBytes As Byte()
            hashWithSaltBytes = Convert.FromBase64String(hashValue)

            ' We must know size of hash (without salt).
            Dim hashSizeInBits As Integer
            Dim hashSizeInBytes As Integer

            ' Make sure that hashing algorithm name is specified.
            If (hashAlgorithm Is Nothing) Then
                hashAlgorithm = ""
            End If

            ' Size of hash is based on the specified algorithm.
            Select Case hashAlgorithm.ToUpper()

                Case "SHA1"
                    hashSizeInBits = 160

                Case "SHA256"
                    hashSizeInBits = 256

                Case "SHA384"
                    hashSizeInBits = 384

                Case "SHA512"
                    hashSizeInBits = 512

                Case Else ' Must be MD5
                    hashSizeInBits = 128

            End Select

            ' Convert size of hash from bits to bytes.
            hashSizeInBytes = hashSizeInBits / 8

            ' Make sure that the specified hash value is long enough.
            If (hashWithSaltBytes.Length < hashSizeInBytes) Then
                VerifyHash = False
            End If

            ' Allocate array to hold original salt bytes retrieved from hash.
            Dim saltBytes() As Byte = New Byte(hashWithSaltBytes.Length - _
                                               hashSizeInBytes - 1) {}

            ' Copy salt from the end of the hash to the new array.
            Dim I As Integer
            For I = 0 To saltBytes.Length - 1
                saltBytes(I) = hashWithSaltBytes(hashSizeInBytes + I)
            Next I

            ' Compute a new hash string.
            Dim expectedHashString As String
            expectedHashString = ComputeHash(plainText, hashAlgorithm, saltBytes)

            ' If the computed hash matches the specified hash,
            ' the plain text value must be correct.
            VerifyHash = (hashValue = expectedHashString)
        End Function
    End Class



End Module


